package com.aizhixin.lab.company.service;

import com.aizhixin.lab.account.dto.AccountDTO;
import com.aizhixin.lab.account.service.AccountService;
import com.aizhixin.lab.common.core.ApiReturnConstants;
import com.aizhixin.lab.common.core.DataValidity;
import com.aizhixin.lab.common.core.PageData;
import com.aizhixin.lab.common.core.UserInforCore;
import com.aizhixin.lab.common.dto.SortDTO;
import com.aizhixin.lab.common.utils.ExcelUtil;
import com.aizhixin.lab.common.utils.PageJdbcUtil;
import com.aizhixin.lab.company.domain.AccountDomain;
import com.aizhixin.lab.company.domain.QueryUserDomain;
import com.aizhixin.lab.company.domain.UserDomain;
import com.aizhixin.lab.company.entity.Classes;
import com.aizhixin.lab.company.entity.College;
import com.aizhixin.lab.company.entity.Professional;
import com.aizhixin.lab.company.entity.User;
import com.aizhixin.lab.company.repository.ClassesRepository;
import com.aizhixin.lab.company.repository.CollegeRepository;
import com.aizhixin.lab.company.repository.ProfessionalRepository;
import com.aizhixin.lab.company.repository.UserRepository;
import com.aizhixin.lab.config.Config;
import com.aizhixin.lab.course.classes.service.CourseClassStuService;
import com.aizhixin.lab.course.classes.service.LabReportService;


import com.aizhixin.lab.jsrun.util.PasswordUtil;
import lombok.extern.slf4j.Slf4j;

import org.apache.commons.io.FileUtils;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.beans.BeanUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.util.StringUtils;
import org.springframework.web.multipart.MultipartFile;

import java.io.File;
import java.io.IOException;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.DecimalFormat;
import java.util.*;

@Slf4j
@Service
@Transactional
public class UserService {
    @Autowired
    private AccountService accountService;
    @Autowired
    private UserRepository userRepository;
    @Autowired
    private PageJdbcUtil pageJdbcUtil;
    @Autowired
    private CourseClassStuService courseClassStuService;
    @Autowired
    private LabReportService labReportService;
    @Autowired
    private Config config;
    @Autowired
    private CollegeRepository collegeRepository;
    @Autowired
    private ProfessionalRepository professionalRepository;
    @Autowired
    private ClassesRepository classesRepository;

    public User getUser(AccountDTO account, Long userId) {
        return userRepository.findOne(userId);
    }

    public User findByUserName(String userName) {
        return userRepository.findByUserName(userName);
    }

    public User findByJobNum(String jobNum) {
        return userRepository.findByJobNumAndDeleteFlag(jobNum, DataValidity.VALID.getState());
    }

    public User save(User user) {
        return userRepository.save(user);
    }

    public Map<String, Object> updatePassword(Long userId, String password, String newPassword) {
        Map<String, Object> result = new HashMap<>();
        User user = userRepository.findOne(userId);
        if (user != null) {
            //登录验证旧密码
            if (accountService.checkOldPassword(user, password)) {
                boolean flag = accountService.updatePassword(user, newPassword);
                if (flag) {
                    result.put(ApiReturnConstants.SUCCESS, true);
                } else {
                    result.put(ApiReturnConstants.SUCCESS, false);
                    result.put(ApiReturnConstants.ERROR, "修改密码失败");
                }
            } else {
                result.put(ApiReturnConstants.SUCCESS, false);
                result.put(ApiReturnConstants.ERROR, "原密码输入错误");
            }
        } else {
            result.put(ApiReturnConstants.SUCCESS, false);
            result.put(ApiReturnConstants.ERROR, "没有用户信息");
        }
        return result;
    }

    public Map<String, Object> resetPassword(Long userId) {
        Map<String, Object> result = new HashMap<>();
        User user = userRepository.findOne(userId);
        if (user != null) {
            //重置密码
            boolean flag = accountService.updatePassword(user, "dingli123");
            if (flag) {
                result.put(ApiReturnConstants.SUCCESS, true);
            } else {
                result.put(ApiReturnConstants.SUCCESS, false);
                result.put(ApiReturnConstants.ERROR, "重置密码失败");
            }
        } else {
            result.put(ApiReturnConstants.SUCCESS, false);
            result.put(ApiReturnConstants.ERROR, "没有用户信息");
        }
        return result;
    }

    public Map<String, Object> delete(Long userId) {
        Map<String, Object> result = new HashMap<>();
        User user = userRepository.findOne(userId);
        if (user != null) {
            //删除知新用户
//            boolean flag = accountService.deleteZXAccount(user);
//            if (flag) {
            //删除本地用户
            user.setDeleteFlag(DataValidity.INVALID.getState());
            userRepository.save(user);
            if (UserInforCore.ROLE_STUDENT.equals(user.getRole())) {
                ArrayList<Long> userIds = new ArrayList<Long>();
                userIds.add(user.getId());
                labReportService.deleteByStuIds(userIds);
                courseClassStuService.deleteByStuIds(userIds);
            }
            result.put(ApiReturnConstants.SUCCESS, true);
//            } else {
//                result.put(ApiReturnConstants.SUCCESS, false);
//                result.put(ApiReturnConstants.ERROR, "删除账号错误");
//            }
        } else {
            result.put(ApiReturnConstants.SUCCESS, false);
            result.put(ApiReturnConstants.ERROR, "没有用户信息");
        }
        return result;
    }


    public Map<String, Object> deleteAll(List<Long> userIds, String role) {
        Map<String, Object> result = new HashMap<>();
        List<User> userList = userRepository.findAllByIdIn(userIds);
        List<User> delUserList = new ArrayList<User>();
        for (User user : userList) {
            if (user != null) {
                //删除知新用户
                //boolean flag = accountService.deleteZXAccount(user);
                //删除本地用户
                user.setDeleteFlag(DataValidity.INVALID.getState());
                delUserList.add(user);

            }
        }
        if (!delUserList.isEmpty()) {
            userRepository.save(userList);
            if (!StringUtils.isEmpty(role)) {
                if (UserInforCore.ROLE_STUDENT.equals(role)) {
                    labReportService.deleteByStuIds(userIds);
                    courseClassStuService.deleteByStuIds(userIds);
                }
            }
        }
        result.put(ApiReturnConstants.SUCCESS, true);
        return result;
    }

    public Map<String, Object> addAccount(AccountDTO dto) {
        Map<String, Object> result = new HashMap<>();
        User user = userRepository.findByUserNameAndDeleteFlagNot(dto.getUserName(), DataValidity.INVALID.getState());
        if (user == null) {
            Long accountId = accountService.addZXAccount(dto);
            if (accountId != null && accountId > 0) {
                user = new User();
                BeanUtils.copyProperties(dto, user);
                user.setDeleteFlag(DataValidity.VALID.getState());
                user.setAccountId(accountId);
                userRepository.save(user);
                result.put(ApiReturnConstants.DATA, user.getUserName());
                result.put(ApiReturnConstants.SUCCESS, true);
            } else {
                result.put(ApiReturnConstants.SUCCESS, false);
                result.put(ApiReturnConstants.ERROR, "创建用户信息错误");
            }
        } else {
            result.put(ApiReturnConstants.SUCCESS, false);
            result.put(ApiReturnConstants.ERROR, "账号已存在!");
        }
        return result;
    }

    public Map<String, Object> addAccount(List<AccountDTO> dtoList) {
        Map<String, Object> result = new HashMap<>();
        List<AccountDTO> list = new ArrayList<>();
        for (AccountDTO dto : dtoList) {
            User user = userRepository.findByUserNameAndDeleteFlagNot(dto.getUserName(), DataValidity.INVALID.getState());
            User user1 = userRepository.findByJobNumAndDeleteFlag(dto.getJobNum(), DataValidity.VALID.getState());
            if (user == null &&user1==null) {
//                Long accountId = accountService.addZXAccount(dto);
//                if (accountId != null && accountId > 0) {
                user = new User();
                BeanUtils.copyProperties(dto, user);
                user.setPassWord(dto.getPassword());
//                String salt = "ad9325e6";
//                String passwordEncode = PasswordUtil.encrypt(user.getUserName(), "dingli123", salt);
//                user.setPassWord(passwordEncode);
                user.setDeleteFlag(DataValidity.VALID.getState());
//                    user.setAccountId(accountId);
                userRepository.save(user);
//                } else {
//                   list.add(dto);
//                }
            } else {
                list.add(dto);
            }
        }
        result.put(ApiReturnConstants.DATA, list);
        result.put(ApiReturnConstants.SUCCESS, true);
        return result;
    }


    public Map<String, Object> initAdmin() {
        Map<String, Object> result = new HashMap<>();
        User user = userRepository.findByUserNameAndDeleteFlagNot("labadmin", DataValidity.INVALID.getState());
        if (user == null) {
            AccountDTO dto = new AccountDTO();
            dto.setUserName("labadmin");
            dto.setName("labadmin");
            dto.setRole("superadmin");


            Long accountId = accountService.addZXAccount(dto);
            if (accountId != null && accountId > 0) {
                user = new User();
                BeanUtils.copyProperties(dto, user);
                user.setAccountId(accountId);
                userRepository.save(user);
                result.put(ApiReturnConstants.DATA, user.getUserName());
                result.put(ApiReturnConstants.SUCCESS, true);
            } else {
                result.put(ApiReturnConstants.SUCCESS, false);
                result.put(ApiReturnConstants.ERROR, "注册用户信息错误");
            }
        } else {
            result.put(ApiReturnConstants.SUCCESS, false);
            result.put(ApiReturnConstants.ERROR, "超级管理员账号已初始化!");
        }
        return result;
    }

    public Map<String, Object> editAccount(AccountDTO dto) {
        Map<String, Object> result = new HashMap<>();
        User user = userRepository.findByIdAndDeleteFlag(dto.getId(), DataValidity.VALID.getState());
        if (user != null) {
            if (!StringUtils.isEmpty(dto.getName())) {
                user.setName(dto.getName());
            }
            if (!StringUtils.isEmpty(dto.getJobNum())) {

                user.setJobNum(dto.getJobNum());
                if (!user.getRole().equals(UserInforCore.ROLE_ADMIN)&&!user.getRole().equals(UserInforCore.ROLE_SUPER_ADMIN)){
                    if (user.getRole().equals(UserInforCore.ROLE_STUDENT)){
                        user.setUserName("stu"+dto.getJobNum());
                    }else {
                        user.setUserName("tea"+dto.getJobNum());
                    }
                }

            }
            if (!StringUtils.isEmpty(dto.getUserName())) {
                User user1 = userRepository.findByUserNameAndDeleteFlagAndIdNot(dto.getUserName(), DataValidity.VALID.getState(), dto.getId());
                if (Objects.isNull(user1)) {
                    user.setUserName(dto.getUserName());
                    user.setPassWord(PasswordUtil.encrypt(user.getUserName(),"dingli123", "ad9325e6"));
                } else {
                    result.put(ApiReturnConstants.SUCCESS, false);
                    result.put(ApiReturnConstants.ERROR, "账号已存在!");
                    return result;
                }
            }
//             if(!StringUtils.isEmpty(dto.getEmail())) {
            user.setEmail(dto.getEmail());
//             }

//             if(!StringUtils.isEmpty(dto.getPhone())) {
            user.setPhone(dto.getPhone());
//             }
            if (!StringUtils.isEmpty(dto.getSex())) {
                user.setSex(dto.getSex());
            }
            if (null != dto.getClassId() && dto.getClassId() > 0) {
                user.setClassId(dto.getClassId());
            }
            if (null != dto.getCollegeId() && dto.getCollegeId() > 0) {
                user.setCollegeId(dto.getCollegeId());
            }
            if (null != dto.getProfessionalId() && dto.getProfessionalId() > 0) {
                user.setProfessionalId(dto.getProfessionalId());
            }
            if (!StringUtils.isEmpty(dto.getAvatar())) {
                user.setAvatar(dto.getAvatar());
            }
            user.setPersonalProfile(dto.getPersonalProfile());
            userRepository.save(user);
            result.put(ApiReturnConstants.SUCCESS, true);
//            accountService.delRedisAccountDTO(user.getAccountId());
        } else {
            result.put(ApiReturnConstants.SUCCESS, false);
            result.put(ApiReturnConstants.ERROR, "用户数据不存在!");
        }
        return result;
    }

    public Map<String, Object> disableAccount(Long userId, Integer status) {
        Map<String, Object> result = new HashMap<>();
        User user = userRepository.findOne(userId);
        if (user != null) {
            user.setDeleteFlag(status);
            userRepository.save(user);
            //accountService.delRedisAccountDTO(user.getAccountId());
            result.put(ApiReturnConstants.SUCCESS, true);
        } else {
            result.put(ApiReturnConstants.SUCCESS, false);
            result.put(ApiReturnConstants.ERROR, "用户数据不存在!");
        }
        return result;
    }

    public Map<String, Object> getAccount(Long userId) {
        Map<String, Object> result = new HashMap<>();
        User user = userRepository.findOne(userId);
        if (user != null) {
            result.put(ApiReturnConstants.DATA, user);
            result.put(ApiReturnConstants.SUCCESS, true);
        } else {
            result.put(ApiReturnConstants.SUCCESS, false);
            result.put(ApiReturnConstants.ERROR, "用户数据不存在!");
        }
        return result;
    }


    RowMapper<AccountDomain> rm = new RowMapper<AccountDomain>() {

        @Override
        public AccountDomain mapRow(ResultSet rs, int rowNum)
                throws SQLException {
            // TODO Auto-generated method stub
            AccountDomain domain = new AccountDomain();
            domain.setId(rs.getLong("ID"));
            domain.setUserName(rs.getString("USER_NAME"));
            domain.setName(rs.getString("NAME"));
            domain.setPhone(rs.getString("PHONE"));
            domain.setEmail(rs.getString("EMAIL"));
            domain.setCreatedDate(rs.getTimestamp("CREATED_DATE"));
            domain.setDeleteFlag(rs.getInt("DELETE_FLAG"));
            return domain;
        }
    };

    public <T> Map<String, Object> findAdminPage(QueryUserDomain domain) {

        String querySql = "SELECT ID,USER_NAME,NAME,PHONE,EMAIL,CREATED_DATE,DELETE_FLAG from `t_user` u where u.role = '" + UserInforCore.ROLE_ADMIN + "' ";
        String countSql = "SELECT count(1)  from `t_user` u where u.role = '" + UserInforCore.ROLE_ADMIN + "' ";
        if (!StringUtils.isEmpty(domain.getName())) {
            querySql += " and u.NAME like '%" + domain.getName() + "%'";
            countSql += " and u.NAME like '%" + domain.getName() + "%'";
        }
        if (null != domain.getStatus() && domain.getStatus().intValue() > -1L) {
            querySql += " and u.delete_flag=" + domain.getStatus() + "";
            countSql += " and u.delete_flag=" + domain.getStatus() + "";
        } else {
            querySql += " and u.delete_flag in(0,2)";
            countSql += " and u.delete_flag in(0,2)";
        }
        List<SortDTO> sort = new ArrayList<SortDTO>();
        SortDTO dto = new SortDTO();
        dto = new SortDTO();
        dto.setKey("u.ID");
        dto.setAsc(false);
        sort.add(dto);

        return pageJdbcUtil.getPageInfor(domain.getPageSize(),
                domain.getPageNumber(), rm, sort, querySql, countSql);
    }


    RowMapper<AccountDomain> rm1 = new RowMapper<AccountDomain>() {

        @Override
        public AccountDomain mapRow(ResultSet rs, int rowNum)
                throws SQLException {
            // TODO Auto-generated method stub
            AccountDomain domain = new AccountDomain();
            domain.setId(rs.getLong("ID"));
            domain.setCollegeId(rs.getLong("COLLEGE_ID"));
            domain.setJobNum(rs.getString("JOB_NUM"));
            domain.setSex(rs.getString("SEX"));
            domain.setCollegeName(rs.getString("COLLEGE_NAME"));
            domain.setName(rs.getString("NAME"));
            domain.setPhone(rs.getString("PHONE"));
            domain.setEmail(rs.getString("EMAIL"));
            domain.setUserName(rs.getString("user_name"));
            return domain;
        }
    };

    public <T> Map<String, Object> findTeacherPage(QueryUserDomain domain) {

        String querySql = "SELECT U.ID,U.NAME,PHONE,EMAIL,JOB_NUM,SEX,c.NAME AS COLLEGE_NAME,u.COLLEGE_ID,user_name from `t_user` u left join `t_college` c on u.COLLEGE_ID = c.id where u.role = '" + UserInforCore.ROLE_TEACHER + "' and u.delete_flag = " + DataValidity.VALID.getState() + " ";
        String countSql = "SELECT count(1)  from `t_user` u  where u.role = '" + UserInforCore.ROLE_TEACHER + "' and u.delete_flag = " + DataValidity.VALID.getState() + " ";
        if (!StringUtils.isEmpty(domain.getName())) {
            querySql += " and (u.NAME like '%" + domain.getName() + "%' OR u.JOB_NUM like '%" + domain.getName() + "%') ";
            countSql += " and (u.NAME like '%" + domain.getName() + "%' OR u.JOB_NUM like '%" + domain.getName() + "%') ";
        }
        if (null != domain.getCollegeId() && domain.getCollegeId().intValue() > 0L) {
            querySql += " and u.COLLEGE_ID =" + domain.getCollegeId() + "";
            countSql += " and u.COLLEGE_ID =" + domain.getCollegeId() + "";
        }
        List<SortDTO> sort = new ArrayList<SortDTO>();
        SortDTO dto = new SortDTO();
        dto = new SortDTO();
        dto.setKey("u.ID");
        dto.setAsc(false);
        sort.add(dto);

        return pageJdbcUtil.getPageInfor(domain.getPageSize(),
                domain.getPageNumber(), rm1, sort, querySql, countSql);
    }


    RowMapper<AccountDomain> rm2 = new RowMapper<AccountDomain>() {

        @Override
        public AccountDomain mapRow(ResultSet rs, int rowNum)
                throws SQLException {
            // TODO Auto-generated method stub
            AccountDomain domain = new AccountDomain();
            domain.setId(rs.getLong("ID"));
            domain.setCollegeId(rs.getLong("COLLEGE_ID"));
            domain.setProfessionalId(rs.getLong("PROFESSIONAL_ID"));
            domain.setJobNum(rs.getString("JOB_NUM"));
            domain.setSex(rs.getString("SEX"));
            domain.setCollegeName(rs.getString("COLLEGE_NAME"));
            domain.setProfessionalName(rs.getString("PROFESSIONAL_NAME"));
            domain.setName(rs.getString("NAME"));
            domain.setPhone(rs.getString("PHONE"));
            domain.setEmail(rs.getString("EMAIL"));
            domain.setClassId(rs.getLong("CLASS_ID"));
            domain.setClassName(rs.getString("CLASS_NAME"));
            domain.setDeleteFlag(rs.getInt("DELETE_FLAG"));
            domain.setUserName(rs.getString("user_name"));
            return domain;
        }
    };

    public <T> Map<String, Object> findStudentPage(QueryUserDomain domain) {

        String querySql = "SELECT U.ID,U.NAME,PHONE,EMAIL,JOB_NUM,SEX,c.NAME AS COLLEGE_NAME,user_name,p.NAME AS PROFESSIONAL_NAME,u.DELETE_FLAG,u.COLLEGE_ID,u.PROFESSIONAL_ID,u.CLASS_ID,cl.name as CLASS_NAME from `t_user` u left join `t_college` c on u.COLLEGE_ID = c.id LEFT JOIN `t_professional` p ON u.PROFESSIONAL_ID = p.ID left join `t_classes` cl on u.class_id =cl.id where u.role = '" + UserInforCore.ROLE_STUDENT + "' ";
        String countSql = "SELECT count(1)  from `t_user` u where u.role = '" + UserInforCore.ROLE_STUDENT + "' ";
        if (!StringUtils.isEmpty(domain.getName())) {
            querySql += " and (u.NAME like '%" + domain.getName() + "%' OR u.JOB_NUM like '%" + domain.getName() + "%') ";
            countSql += " and (u.NAME like '%" + domain.getName() + "%' OR u.JOB_NUM like '%" + domain.getName() + "%') ";
        }
        if (null != domain.getCollegeId() && domain.getCollegeId().intValue() > 0L) {
            querySql += " and u.COLLEGE_ID =" + domain.getCollegeId() + "";
            countSql += " and u.COLLEGE_ID =" + domain.getCollegeId() + "";
        }
        if (null != domain.getProfessionalId() && domain.getProfessionalId().intValue() > 0L) {
            querySql += " and u.PROFESSIONAL_ID =" + domain.getProfessionalId() + "";
            countSql += " and u.PROFESSIONAL_ID =" + domain.getProfessionalId() + "";
        }
        if (null != domain.getClassId() && domain.getClassId().intValue() > 0L) {
            querySql += " and u.CLASS_ID =" + domain.getClassId() + "";
            countSql += " and u.CLASS_ID =" + domain.getClassId() + "";
        }
        if (null != domain.getStatus() && domain.getStatus().intValue() > -1L) {
            querySql += " and u.delete_flag=" + domain.getStatus() + "";
            countSql += " and u.delete_flag=" + domain.getStatus() + "";
        } else {
            querySql += " and u.delete_flag in(0,2)";
            countSql += " and u.delete_flag in(0,2)";
        }
        List<SortDTO> sort = new ArrayList<SortDTO>();
        SortDTO dto = new SortDTO();
        dto = new SortDTO();
        dto.setKey("u.ID");
        dto.setAsc(false);
        sort.add(dto);

        return pageJdbcUtil.getPageInfor(domain.getPageSize(),
                domain.getPageNumber(), rm2, sort, querySql, countSql);
    }


    RowMapper<Long> rm3 = new RowMapper<Long>() {

        @Override
        public Long mapRow(ResultSet rs, int rowNum)
                throws SQLException {
            return rs.getLong("ID");
        }
    };

    /**
     * 将excel文件转为List集合
     *
     * @param file
     * @return
     */
    public List fileTransformList(MultipartFile file, AccountDTO dto) throws Exception {
        Map<String,Long> classIdMap=new HashMap<>();
        Map<String,Long> collegeMap=new HashMap<>();
        Map<String,Long> professionalMap=new HashMap<>();

        List<College> collegeList = collegeRepository.findAllByDeleteFlagOrderById(DataValidity.VALID.getState());
        List<Professional> professionalList = professionalRepository.findAllByDeleteFlagOrderById(DataValidity.VALID.getState());
        List<Classes> classesList = classesRepository.findByDeleteFlag(DataValidity.VALID.getState());
        classesList.forEach(item->{
            classIdMap.put(item.getName(),item.getId());
        });
        collegeList.forEach(item->{
            collegeMap.put(item.getName(),item.getId());
        });
        professionalList.forEach(item->{
            professionalMap.put(item.getName(),item.getId());
        });
        File dest = new File(config.getBaseDir() + "/" + file.getOriginalFilename());

        try {
            FileUtils.copyInputStreamToFile(file.getInputStream(), dest);
        } catch (Exception e) {
            e.printStackTrace();
        }
        Workbook workbook;
        if (dest.getName().endsWith(".xls")) {
            workbook = new HSSFWorkbook(file.getInputStream());
        } else {
            workbook = new XSSFWorkbook(file.getInputStream());
        }
        ExcelUtil excelUtil = new ExcelUtil(workbook);
        DecimalFormat df = new DecimalFormat("0");
        Sheet sheet = workbook.getSheetAt(0);
        List<AccountDTO> accountDTOS = new ArrayList<>();
        int rows = sheet.getLastRowNum();
        for (int i = 1; i <= rows; i++) {
            if (sheet.getRow(i) == null || sheet.getRow(i).getCell(0) == null || sheet.getRow(i).getCell(0).getCellType() == Cell.CELL_TYPE_BLANK) {
                continue;
            }
            AccountDTO accountDTO = new AccountDTO();
            accountDTO.setJobNum(excelUtil.getCellValueToString(sheet.getRow(i).getCell(0)).trim());
            if (dto.getRole().equals(UserInforCore.ROLE_STUDENT)) {
                accountDTO.setUserName("stu" + accountDTO.getJobNum());
            }else {
                accountDTO.setUserName("tea" + accountDTO.getJobNum());
            }

            accountDTO.setName(excelUtil.getCellValueToString(sheet.getRow(i).getCell(1)));
            accountDTO.setSex(excelUtil.getCellValueToString(sheet.getRow(i).getCell(2)));
            accountDTO.setPhone(excelUtil.getCellValueToString(sheet.getRow(i).getCell(3)));
            String pro = excelUtil.getCellValueToString(sheet.getRow(i).getCell(4));
            String className = excelUtil.getCellValueToString(sheet.getRow(i).getCell(5));
            String collegeName = excelUtil.getCellValueToString(sheet.getRow(i).getCell(7));
            accountDTO.setProfessionalId(professionalMap.get(pro));
            accountDTO.setCollegeId(collegeMap.get(collegeName));
            if (accountDTO.getProfessionalId()==null||accountDTO.getCollegeId()==null){
                throw new Exception("第"+i+"行学院或专业不存在");
            }
            accountDTO.setRole(dto.getRole());
            if (accountDTO.getRole().equals(UserInforCore.ROLE_STUDENT)) {
                accountDTO.setClassId(classIdMap.get(className));
                if (accountDTO.getClassId() == null) {
                    Classes classes = new Classes();
                    classes.setName(className);
                    classes.setCollegeId(accountDTO.getCollegeId());
                    classes.setProfessionalId(accountDTO.getProfessionalId());
                    classesRepository.save(classes);
                    accountDTO.setClassId(classes.getId());
                    classIdMap.put(classes.getName(), classes.getId());
                }
            }
            String salt = "ad9325e6";
            String passwordEncode = PasswordUtil.encrypt(accountDTO.getUserName(),"dingli123", salt);
//            accountDTO.setSex(sheet.getRow(i).getCell(3).toString());
//            accountDTO.setPhone(excelUtil.getCellValueToString(sheet.getRow(i).getCell(6)));
//            accountDTO.setEmail(sheetgetRow.(i).getCell(5).toString());
            accountDTO.setPassword(passwordEncode);

            accountDTO.setDeleteFlag(0);
            accountDTOS.add(accountDTO);


        }

        return accountDTOS;
    }

    RowMapper<UserDomain> ud = new RowMapper<UserDomain>() {

        @Override
        public UserDomain mapRow(ResultSet rs, int rowNum)
                throws SQLException {
            // TODO Auto-generated method stub
            UserDomain domain = new UserDomain();
            domain.setId(rs.getLong("id"));
            domain.setCollegeName(rs.getString("college_name"));
            domain.setProName(rs.getString("pro_name"));
            domain.setName(rs.getString("user_name"));
            domain.setClassName(rs.getString("class_name"));
            return domain;
        }
    };

    public UserDomain getUserInfo(Long userId) {
        String sql = "SELECT tu.id ,tcc.`NAME` college_name,tp.`NAME` pro_name,tc.`NAME` class_name,tu.`name` user_name FROM t_user tu LEFT JOIN t_classes tc on tu.CLASS_ID=tc.ID LEFT JOIN t_professional tp ON tp.ID=tu.PROFESSIONAL_ID LEFT JOIN t_college tcc ON tcc.ID=tu.COLLEGE_ID where tu.id=" + userId;
        List<UserDomain> info = pageJdbcUtil.getInfo(sql, ud);
        return info.get(0);
    }

    public List<Long> getUserIdByClassId(Long classId) {
        String sql = "select `id` from `t_user` where delete_flag =0 and class_id= " + classId;
        return pageJdbcUtil.getInfo(sql, rm3);
    }

    public Long countByClassId(Long classId) {
        return userRepository.countByClassIdAndDeleteFlag(classId, DataValidity.VALID.getState());
    }

    public Long countByClassIdIn(List<Long> classIds) {
        return userRepository.countByClassIdInAndDeleteFlag(classIds, DataValidity.VALID.getState());
    }

    public Long countByProfessionalId(Long professionalId) {
        return userRepository.countByProfessionalIdAndDeleteFlagAndRole(professionalId, DataValidity.VALID.getState(),"student");
    }

    public Long countByCollegeId(Long collegeId) {
        return userRepository.countByCollegeIdAndDeleteFlag(collegeId, DataValidity.VALID.getState());
    }

    public List<User> findUsers() {
        return userRepository.findByDeleteFlag(DataValidity.VALID.getState());
    }


}
